System and method for providing bottom-up aggregation in a multidimensional database environment

ABSTRACT

Systems and methods for supporting bottom-up aggregation in a multidimensional database computing environment. A dynamic flow, coupled with a data retrieval layer or data fetching component, which in some environments can incorporate a kernel-based data structure, referred to herein as an odometer retriever, or odometer, that manages pointers to data blocks, contains control information, or otherwise operates as an array of arrays of pointers to stored members, enables bottom-up aggregation of cube data which, for example with pure aggregating queries, provides considerable run time improvement.

CLAIM OF PRIORITY AND CROSS-REFERENCE TO RELATED APPLICATIONS

This application is a continuation of and claims the benefit of priorityto U.S. patent application titled “SYSTEM AND METHOD FOR PROVIDINGBOTTOM-UP AGGREGATION IN A MULTIDIMENSIONAL DATABASE ENVIRONMENT”,application Ser. No. 15/333,022, filed Oct. 24, 2016; which applicationclaims the benefit of priority to U.S. Provisional Patent Applicationtitled “SYSTEM AND METHOD FOR HYBRID SUPPORT FOR DYNAMIC UPPER LEVELMEMBERS WITH A MULTIDIMENSIONAL DATABASE”, Application No. 62/245,892,filed Oct. 23, 2015; U.S. Provisional Patent Application titled “SYSTEMAND METHOD FOR HIGH PERFORMANCE BOTTOM-UP AGGREGATION IN AMULTIDIMENSIONAL DATABASE ENVIRONMENT”, Application No. 62/245,897,filed Oct. 23, 2015; U.S. Provisional Patent Application titled “SYSTEMAND METHOD FOR SUPPORT FOR LARGE QUERIES IN A MULTIDIMENSIONAL DATABASEENVIRONMENT”, Application No. 62/245,901, filed Oct. 23, 2015; and U.S.Provisional Patent Application titled “SYSTEM AND METHOD FOR PROVIDING AMULTIDIMENSIONAL DATABASE”, Application No. 62/411,473, filed Oct. 21,2016; and is related to U.S. patent application titled “SYSTEM ANDMETHOD FOR USE OF A DYNAMIC FLOW IN A MULTIDIMENSIONAL DATABASEENVIRONMENT”, application Ser. No. 15/332,948, filed Oct. 24, 2016 andsubsequently issued as U.S. Pat. No. 10,552,393 on Feb. 4, 2020; each ofwhich above applications are herein incorporated by reference.

COPYRIGHT NOTICE

A portion of the disclosure of this patent document contains materialwhich is subject to copyright protection. The copyright owner has noobjection to the facsimile reproduction by anyone of the patent documentor the patent disclosure, as it appears in the Patent and TrademarkOffice patent file or records, but otherwise reserves all copyrightrights whatsoever.

FIELD OF INVENTION

Embodiments of the invention are generally related to multidimensionaldatabase computing environments, and are particularly related to asystem and method for providing bottom-up aggregation in amultidimensional database environment.

BACKGROUND

Multidimensional database computing environments enable companies todeliver critical business information to the right people when they needit, including the ability to leverage and integrate data from multipleexisting data sources, and distribute filtered information to end-usercommunities in a format that best meets those users' needs. Users caninteract with and explore data in real time, and along familiar businessdimensions, enabling speed-of-thought analytics. These are some examplesof the types of environment in which embodiments of the invention can beused.

SUMMARY

In accordance with an embodiment, the system supports bottom-upaggregation in a multidimensional database (e.g., Essbase) computingenvironment. A dynamic flow, coupled with a data retrieval layer or datafetching component (which in some environments can incorporate akernel-based odometer retriever, or odometer that manages pointers todata blocks, contains control information, or otherwise acts as an arrayof arrays of pointers to stored members) enables bottom-up aggregationof cube data which, for example with pure aggregating queries, providesconsiderable run time improvement.

BRIEF DESCRIPTION OF THE FIGURES

FIG. 1 illustrates an example of a multidimensional databaseenvironment, in accordance with an embodiment.

FIG. 2 illustrates use of a dynamic flow with a multidimensionaldatabase, in accordance with an embodiment.

FIG. 3 further illustrates use of a dynamic flow with a multidimensionaldatabase, in accordance with an embodiment.

FIG. 4 further illustrates use of a dynamic flow with a multidimensionaldatabase, in accordance with an embodiment.

FIG. 5 further illustrates use of a dynamic flow with a multidimensionaldatabase, in accordance with an embodiment.

FIG. 6 illustrates a process for use of a dynamic flow with amultidimensional database, in accordance with an embodiment.

FIG. 7 illustrates bottom-up aggregation with a multidimensionaldatabase, in accordance with an embodiment.

FIG. 8 further illustrates bottom-up aggregation with a multidimensionaldatabase, in accordance with an embodiment.

FIG. 9 further illustrates bottom-up aggregation with a multidimensionaldatabase, in accordance with an embodiment.

FIG. 10 illustrates a process for bottom-up aggregation with amultidimensional database, in accordance with an embodiment.

DETAILED DESCRIPTION

The foregoing, together with other features, will become apparent uponreferring to the enclosed specification, claims, and drawings. Specificdetails are set forth in order to provide an understanding of variousembodiments. However, it will be apparent that various embodiments maybe practiced without these specific details. The enclosed specificationand drawings are not intended to be restrictive.

Multidimensional database environments, an example of which includesOracle Essbase, can be used to integrate large amounts of data, in someinstances from multiple data sources, and distribute filteredinformation to end-users, in a manner that addresses those users'particular requirements.

FIG. 1 illustrates an example of a multidimensional database environment100, in accordance with an embodiment.

As illustrated in FIG. 1 , in accordance with an embodiment, amultidimensional database environment, operating as a database tier, caninclude one or more multidimensional database server system(s) 102, eachof which can include physical computer resources or components 104(e.g., microprocessor/CPU, physical memory, network components), anoperating system 106, and one or more multidimensional databaseserver(s) 110 (e.g., Essbase Servers).

In accordance with an embodiment, a middle tier 120 can include one ormore service(s), such as, for example, provider services 122 (e.g.,Hyperion Provider Services), administration services 124 (e.g., EssbaseAdministration Services), or studio/integration services 126 (e.g.,Essbase Studio/Essbase Integration Services). The middle tier canprovide access, via ODBC/JDBC 127, 128, or other types of interfaces, toa metadata catalog 129, and/or one or more data source(s) 130 (forexample, a relational database), for use with the multidimensionaldatabase environment.

In accordance with an embodiment, the one or more data source(s) canalso be accessed, via ODBC/JDBC 132, or other types of interfaces, bythe one or more multidimensional database server(s), for use inproviding a multidimensional database.

In accordance with an embodiment, a client tier 140 can include one ormore multidimensional database client(s) 142 (e.g., Essbase Serverclients), that enable access to a multidimensional database (such as,for example, Smart View, Spreadsheet Add-in, Smart Search,Administration Services, MaxL, XMLA, CAPI or VB API Applications, OracleBusiness Intelligence Enterprise Edition Plus, or other types ofmultidimensional database clients). The client tier can also includeconsoles, for use with services in the middle tier, such as for examplean administration services console 144, or a studio/integration servicesconsole 146.

In accordance with an embodiment, communication between the client,middle, and database tiers can be provided by one or more of TCP/IP,HTTP, or other types of network communication protocols.

In accordance with an embodiment, the multidimensional database servercan integrate data from the one or more data source(s), to provide amultidimensional database, data structure, or cube(s) 150, which canthen be accessed to provide filtered information to end-users.

Generally, each data value in a multidimensional database is stored inone cell of a cube; and a particular data value can be referenced byspecifying its coordinates along dimensions of the cube. Theintersection of a member from one dimension, with a member from each ofone or more other dimensions, represents a data value.

For example, as illustrated in FIG. 1 , which illustrates a cube 162that might be used in a sales-oriented business application, when aquery indicates “Sales”, the system can interpret this query as a sliceor layer of data values 164 within the database that contains all“Sales” data values, where “Sales” intersect with “Actual” and “Budget”.To refer to a specific data value 166 in a multidimensional database,the query can specify a member on each dimension, for example byspecifying “Sales, Actual, January”. Slicing the database in differentways, provides different perspectives of the data; for example, a sliceof data values 168 for “February” examines all of those data values forwhich a time/year dimension is fixed for “February”.

Database Outline

In accordance with an embodiment, development of a multidimensionaldatabase begins with the creation of a database outline, which definesstructural relationships between members in the database; organizes datain the database; and defines consolidations and mathematicalrelationships. Within the hierarchical tree or data structure of thedatabase outline, each dimension comprises one or more members, which inturn may comprise other members. The specification of a dimensioninstructs the system how to consolidate the values of its individualmembers. A consolidation is a group of members within a branch of thetree.

Dimensions and Members

In accordance with an embodiment, a dimension represents the highestconsolidation level in the database outline. Standard dimensions may bechosen to represent components of a business plan that relate todepartmental functions (e.g., Time, Accounts, Product Line, Market,Division). Attribute dimensions, that are associated with standarddimensions, enable a user to group and analyze members of standarddimensions based on member attributes or characteristics. Members (e.g.,Product A, Product B, Product C) are the individual components of adimension.

Dimension and Member Relationships

In accordance with an embodiment, a multidimensional database usesfamily (parents, children, siblings; descendants and ancestors); andhierarchical (generations and levels; roots and leaves) terms, todescribe the roles and relationships of the members within a databaseoutline.

In accordance with an embodiment, a parent is a member that has a branchbelow it. For example, “Margin” may be a parent for “Sales”, and “Costof Goods Sold” (COGS). A child is a member that has a parent above it.In the above example, “Sales” and “Cost of Goods Sold” are children ofthe parent “Margin”. Siblings are children of the same immediate parent,within the same generation.

In accordance with an embodiment, descendants are members in branchesbelow a parent. For example, “Profit”, “Inventory”, and “Ratios” may bedescendants of Measures; in which case the children of “Profit”,“Inventory”, and “Ratios” are also descendants of Measures. Ancestorsare members in branches above a member. In the above example, “Margin”,“Profit”, and Measures may be ancestors of “Sales”.

In accordance with an embodiment, a root is the top member in a branch.For example, Measures may be the root for “Profit”, “Inventory”, and“Ratios”; and as such for the children of “Profit”, “Inventory”, and“Ratios”. Leaf (level 0) members have no children. For example, Opening“Inventory”, Additions, and Ending “Inventory” may be leaf members.

In accordance with an embodiment, a generation refers to a consolidationlevel within a dimension. The root branch of the tree is considered tobe “generation 1”, and generation numbers increase from the root towarda leaf member. Level refers to a branch within a dimension; and arenumbered in reverse from the numerical ordering used for generations,with level numbers decreasing from a leaf member toward its root.

In accordance with an embodiment, a user can assign a name to ageneration or level, and use that name as a shorthand for all members inthat generation or level.

Sparse and Dense Dimensions

Data sets within a multidimensional database often share twocharacteristics: the data is not smoothly and uniformly distributed; anddata does not exist for a majority of member combinations.

In accordance with an embodiment, to address this, the system canrecognize two types of standard dimensions: sparse dimensions and densedimensions. A sparse dimension is one with a relatively low percentageof available data positions filled; while a dense dimension is one inwhich there is a relatively high probability that one or more cells isoccupied in every combination of dimensions. Many multidimensionaldatabases are inherently sparse, in that they lack data values for themajority of member combinations.

Data Blocks and the Index System

In accordance with an embodiment, the multidimensional database usesdata blocks and an index to store and access data. The system can createa multidimensional array or data block for each unique combination ofsparse standard dimension members, wherein each data block representsthe dense dimension members for its combination of sparse dimensionmembers. An index is created for each data block, wherein the indexrepresents the combinations of sparse standard dimension members, andincludes an entry or pointer for each unique combination of sparsestandard dimension members for which at least one data value exists.

In accordance with an embodiment, when the multidimensional databaseserver searches for a data value, it can use the pointers provided bythe index, to locate the appropriate data block; and, within that datablock, locate the cell containing the data value.

Administration Services

In accordance with an embodiment, an administration service (e.g.,Essbase Administration Services) provides a single-point-of-access thatenables a user to design, develop, maintain, and manage servers,applications, and databases.

Studio

In accordance with an embodiment, a studio (e.g., Essbase Studio)provides a wizard-driven user interface for performing tasks related todata modeling, cube designing, and analytic application construction.

Spreadsheet Add-in

In accordance with an embodiment, a spreadsheet add-in integrates themultidimensional database with a spreadsheet, which provides support forenhanced commands such as Connect, Pivot, Drill-down, and Calculate.

Integration Services

In accordance with an embodiment, an integration service (e.g., EssbaseIntegration Services), provides a metadata-driven environment for use inintegrating between the data stored in a multidimensional database anddata stored in relational databases.

Provider Services

In accordance with an embodiment, a provider service (e.g., HyperionProvider Services) operates as a data-source provider for Java API,Smart View, and XMLA clients.

Smart View

In accordance with an embodiment, a smart view provides a commoninterface for, e.g., Hyperion Financial Management, Hyperion Planning,and Hyperion Enterprise Performance Management Workspace data.

Developer Products

In accordance with an embodiment, developer products enable the rapidcreation, management, and deployment of tailored enterprise analyticapplications.

Lifecycle Management

In accordance with an embodiment, a lifecycle management (e.g., HyperionEnterprise Performance Management System Lifecycle Management) providesa means for enabling enterprise performance management products tomigrate an application, repository, or individual artifacts acrossproduct environments.

OLAP

In accordance with an embodiment, online analytical processing (OLAP)provides an environment that enables users to analyze enterprise data.For example, finance departments can use OLAP for applications such asbudgeting, activity-based costing, financial performance analysis, andfinancial modeling, to provide “just-in-time” information.

Dynamic Flow

In accordance with an embodiment, the system supports the use of adynamic flow (referred to herein in some examples as Query ProcessingDynamic Flow (QPDF)) in a multidimensional database (e.g., Essbase)computing environment. The dynamic flow process enables hybrid use of,for example, Aggregate Storage Option (ASO), Block Storage Option (BSO),or other types of storage containers, and provides a common flow toprocess a received input query in bottom-up mode. The approach can beused to reduce the size of the cube, which provides for efficientcalculation of dynamic members.

For example, in accordance with an embodiment, for queries accessingdynamic sparse members, the system can use an aggregate storage engineto satisfy the request. For queries that cannot be processed by theaggregate storage engine, the system can employ a block storage engineto satisfy the request, including for example bringing the data into anaggregate storage temporary tablespace.

For example, in accordance with an embodiment, when executed by acomputer system, the dynamic flow process can operate upon amultidimensional database to: (1) expand an input query to find allbase/calculated data; (2) analyze the expanded query to finddependencies and an order of calculation; (3) define calculation unitsaccording to the preceding steps; (4) build a processing flow with thedefined calculation units, and connect them; and (5) execute theprocessing flow, and determine a response to the input query.

FIG. 2 illustrates use of a dynamic flow with a multidimensionaldatabase, in accordance with an embodiment.

In a typical multidimensional environment, in order to prepare thesystem to respond to input queries, the database server pre-calculatesvalues for certain dimensions, and stores those pre-calculated values inthe cube for later lookup.

In accordance with an embodiment, when a dynamic flow is instead used,the ability to support dynamic query processing enables the databaseserver to avoid pre-calculating and storing such values, which improvesperformance and reduces the storage of potentially empty cells.

As illustrated in FIG. 2 , in accordance with an embodiment, the systemcan include one or more query processor(s) 200, for example aMultidimensional Expressions (MDX) query processor 202, and/or aSpreadSheet Extractor (SSE) 204 query processor, that enable receipt 206of an input query 208 from a client, to retrieve, access, or otherwiseexamine a set of data from a data source, as provided by and madeaccessible via the multidimensional database.

In accordance with an embodiment, a preprocessor component 210 caninclude a data retrieval layer 212 or data fetching component (which insome environments can incorporate a kernel-based odometer retriever, orodometer or data structure stored in memory that manages pointers todata blocks, contains control information, or otherwise acts as an arrayof arrays of pointers to stored members), an aggregator component 214,and a calculator component 216, each of these layers and components canbe provided as a software or program code that is executable by acomputer system.

Generally, described, in accordance with an embodiment, the preprocessorreceives 218 input queries, from the one or more query processor(s), forprocessing against the multidimensional database. The aggregator isadapted to perform hierarchical aggregations of the data. The calculatoris adapted to perform calculations on the data, and to cooperate withthe aggregator, as further described below, to utilize the dataretrieval layer (including an odometer as appropriate) to at least oneof populate and/or search within a cube, and to process a response to aninput query.

In accordance with an embodiment, the system can include one or morestorage container(s) 220, such as, for example, one or more of anAggregate Storage Option (ASO) 222, Block Storage Option (BSO) 224, orother type of storage container 226, each of which storage containerscan act as an interface between the data that is read from/written to230 the data source or multidimensional database, and whichever datamight be needed for aggregation and calculation at the preprocessor.

FIG. 3 further illustrates use of a dynamic flow with a multidimensionaldatabase, in accordance with an embodiment.

As illustrated in FIG. 3 , in accordance with an embodiment, in responseto the database server receiving an input query, the aggregator canoperate 240, 242 in combination with the calculator, to process thequery, as part of a dynamic flow 244, which can be similarly provided assoftware or program code that is executable by a computer system.

For example, as illustrated in FIG. 3 , the dynamic flow process enableshybrid use of, in this example, one or more ASO, BSO, or other types ofstorage containers, and provides a common flow to process the queryusing these storage containers, in a bottom-up mode.

In accordance with an embodiment, when the system begins to process aninput query, it first determines, from an examination of the inputquery, which particular data or other information needs to be retrieved,i.e., a metadata. The system can then define 246 for that input query,an initial calculation unit 250, which encapsulates anaggregation/calculation process that will retrieve a set of data fromthe storage container(s).

In accordance with an embodiment, a data buffer 260 (referred to herein,in some examples, as one or more output buckets) operates as a datastructure into which each calculation unit can read/write data 252, andwhich allows for a temporary storage of the data received from 254 thestorage container(s), for consumption by calculation unit(s).

In accordance with an embodiment, when the dynamic flow is used with aBSO-type storage container, the dynamic flow process performs apre-analysis of an input query, and an expansion of asked points to itsbase data.

However, the amount of such expanded base data may be quite large.

To address this, and to reduce the volume of expanded data, inaccordance with an embodiment, a first dynamic aggregation can beperformed during the fetching of data from the kernel, without a fullexpansion of the associated kernel structure (such as, for example, akernel-side odometer as described above).

In accordance with an embodiment, the dynamic flow then operates toexpand the input query, to find all base/calculated data; and to analyzethe expanded query to find dependencies and an order of calculation.

FIG. 4 further illustrates use of a dynamic flow with a multidimensionaldatabase, in accordance with an embodiment.

As illustrated in FIG. 4 , in accordance with an embodiment, as eachsubsequent calculation unit 262 is determined by the system as part of adynamic flow instance, the aggregation/calculation process encapsulatedby that calculation unit fills up 263 another output bucket in the databuffer.

In accordance with an embodiment, the dynamic flow process continuesdefining subsequent calculation units; and then connects the calculationunits; and executes the resultant processing flow, to determine aresponse to the input query.

FIG. 5 further illustrates use of a dynamic flow with a multidimensionaldatabase, in accordance with an embodiment.

As illustrated in FIG. 5 , in accordance with an embodiment, once thedynamic flow has been built, and has been processed to completion of aresponse, the preprocessor can create or otherwise populate 266 adatabase cube 270, and the data response 272 can be returned to therequesting client.

In accordance with an embodiment, the dynamic flow can support a varietyof modes: including streaming and non-streaming modes, as furtherdescribed below.

In accordance with an embodiment, in a streaming mode, each calculationunit includes two steps of execution, including that (a) each storedcell is processed according to a calculation unit logic (if it belongsto the unit) and the cell is returned to subsequent/next units and/or anoutput bucket, without a need for temporary storage; and (b) allcalculated dynamic cells are then returned to next units and/or theoutput bucket.

Advantages of the streaming mode include the provision of a streaminginterface; and efficient data processing (for example, there is no needfor temporary storage of unused cells, the mode requires low memoryusage, and each cell will be processed only in relevant units).

In accordance with an embodiment, in a non-streaming mode, eachcalculation unit stores all of its cells in a local temporary buffer,until all of the calculations are finished. Once all of the calculationsare finished, then all calculated points are returned to the next unit,one-by-one (as in the streaming mode).

Advantages of the non-streaming mode include a simpler logic for thecalculation units and their interactions.

FIG. 6 illustrates a process for use of a dynamic flow with amultidimensional database, in accordance with an embodiment.

As illustrated in FIG. 6 , in accordance with an embodiment, at step280, a multidimensional database environment is provided at a computersystem, which enables data to be stored in one or more database cubes,and which enables queries to be received for data in the one or morecubes.

At step 282, a preprocessor, including an aggregator, calculator, andretrieval layer is provided, which operates according to a dynamic flowprocess, for querying one or more ASO, BSO, or other data storageengines, and which processes received input queries in a bottom-up mode.

At step 284, upon receipt of an input query, the input query isexpanded, to find all base/calculated data.

At step 286, the expanded query is analyzed, to find dependencies and anorder of calculation.

At step 287, calculation units are defined, according to the precedingsteps.

At step 288, a processing flow is built, with the defined calculationunits and connecting them in appropriate order.

At step 289, the processing flow is executed, and a response to theinput query is determined.

Odometer Retrieval

In accordance with an embodiment, the system supports bottom-upaggregation in a multidimensional database (e.g., Essbase) computingenvironment. A dynamic flow, coupled with a data retrieval layer or datafetching component (which in some environments can incorporate akernel-based odometer retriever, or odometer that manages pointers todata blocks, contains control information, or otherwise acts as an arrayof arrays of pointers to stored members) enables bottom-up aggregationof cube data which, for example with pure aggregating queries, providesconsiderable run time improvement.

In a multidimensional database environment, aggregation provides severaladvantages, together with some consequences. Although a kernel-basedcomponent, such as an odometer, can fetch stored and partitioned cellsand return them to a next calculation unit; with larger sparsedimensions, larger data sizes, and queries for non-stored members, thesystem may need to pass a large amount of data from the kernel to, e.g.,an aggregator.

Dynamic members could be expanded, for example by producing rangesets ofleaves' member numbers (memnos) for the dynamic members, followed by amerging of these rangesets into a single large rangeset, and providingthat to the odometer retriever.

However, for each stored cell which the odometer retriever passes to theaggregator, the recipient aggregator must generally search for thatcell's ascendants among many rangesets of dynamic members (of whichthere may in turn be a number of ascendants), and perform aggregationsto these ascendants.

To address this, in accordance with an embodiment, a dynamic flow orprocess such as that described above, can be used to expand dynamicmembers down to a selection of aggregated members that are relativelyeasy to obtain.

In this manner, the system can reduce the amount of data that must bepassed from the odometer retriever.

In accordance with an embodiment, the odometer can operate similar to anASO-type flow, in that it need not output any cell, until all of thedata is fetched.

FIG. 7 illustrates bottom-up aggregation with a multidimensionaldatabase, in accordance with an embodiment.

In accordance with an embodiment, the odometer retriever can be usedwith a BSO-type storage container, in which a multidimensional cube maybe represented by smaller building blocks; and wherein a particularblock either has data, or else there is no data in that particularblock.

Since it would generally not be feasible to instantiate all possibleblocks, a BSO-type storage container generally only creates those blocksthat will contribute to the result.

In accordance with an embodiment, the odometer enables the databaseserver to process an input query in a dynamic fashion, includingdetermining quickly which of the blocks in a cube contribute to thatinput query.

For example, a query for cities in California may be processed toinclude only those California cities that will contribute values to thequery.

In accordance with an embodiment, an input query can be represented,e.g. in MDX, and can be used to define a region within themultidimensional database. This region may be illustrated as a subset ofa very large cube, with links to other regions or cubes within thelarger cube. The odometer provides a means of obtaining data from thelarger cube, in a process referred to herein as odometer expansion. Thedatabase server can then retrieve the data provided by the odometerexpansion, from block storage, to respond to the input query.

As illustrated in FIG. 7 , when an input query 290 is processed, aninput odometer 300 is created, and the system determines the blockswithin storage for a retrieved part of the odometer 304.

This first step can be processed relatively quickly, since the databaseserver can touch the data and determine whether it contributes to theresult. If so, the system starts accumulating that data.

FIG. 8 further illustrates bottom-up aggregation with a multidimensionaldatabase, in accordance with an embodiment.

As illustrated in FIG. 8 , odometer expansion is then used 308, tocreate an expanded odometer 310, including determining those blocksassociated with the dynamic part(s) of the odometer 312.

Using the above approach, the odometer enables the system to scan blockstorage, retrieve appropriate blocks, and manipulate the numbers ifnecessary to produce the data asked for, which may not necessarily bethe data scanned from storage.

In particular, in accordance with an embodiment, the odometer enablesthe system to determine which blocks in a block-oriented storagecontribute to the odometer, and scan those blocks while calculating anupper level, to further determine potentially contributing blocks, in abottom-up manner.

As illustrated in FIG. 8 , in accordance with an embodiment, the dataindicated by the odometer expansion 314 can then be determined withinthe cube, and provided as a response to the input query.

FIG. 9 further illustrates bottom-up aggregation with a multidimensionaldatabase, in accordance with an embodiment.

As illustrated in FIG. 9 , an example odometer acts as an array ofarrays of pointers to members. The dynamic flow operates upon an inputodometer 320, and expands the odometer for formulas, aggregations andsolve-orders 322. The input odometer can be sorted/grouped for units,keeping members that are needed for odometer retrieval first indimensions, including, as illustrated in the example of FIG. 9 ,determining a retrieved part of the odometer 324, and one or moredynamic parts of the odometer 328, 330.

In the example odometer illustrated in FIG. 9 , the dimensions' ordermatches the outline, and each dimension is covered by at least adefault/top member. The members for each dimension are sorted by memnoXnumbers in ascending order. For BSO, the system can process sparse anddense dimensions' members that comply with one of the following: storedmembers; dynamic members with no formula, sum-only (“+” or “−”)aggregation; or members defined in a partition target that the dynamicflow does not expand.

The example described in FIG. 9 is provided for purposes ofillustration. In accordance with various embodiments, other types ofodometers and usages can be defined.

FIG. 10 illustrates a process for bottom-up aggregation with amultidimensional database, in accordance with an embodiment.

As illustrated in FIG. 10 , in accordance with an embodiment, at step340, a multidimensional database environment is provided at a computersystem, which enables data to be stored in one or more database cubes,and which enables queries to be received for data in the one or morecubes.

At step 342, a preprocessor, including an aggregator, calculator, andretrieval layer is provided, which operates according to a dynamic flowprocess, for querying one or more ASO, BSO, or other data storageengines, and which processes received input queries in a bottom-up mode.

At step 344, an odometer that manages pointers to data blocks, containscontrol information, or otherwise acts as an array of arrays of pointersto stored members is provided, and an odometer retrieval is used toprocess an input query, including determining which blocks contribute tothe query

At step 346, the odometer can touch or otherwise determine whether adata contributes to the result, and if so start accumulating that datawhile calculating upper level data.

Example Embodiment

In accordance with an example embodiment, the system can include oroperate according to various functionality or functions described below.In accordance with other embodiments, other types of functionality orfunctions can be included.

In accordance with an example embodiment, the odometer is an array ofarrays of pointers to members. The Dynamic Flow expands its inputodometer for formulas, aggregations and solve-orders and then it'ssorted/grouped for units, keeping members that are needed for OdometerRetriever first in dimensions.

In accordance with an example embodiment, an ASO odometer(AD_QXASOMEMBER_T**) will be used for both ASO and BSO cases.

1. The dimensions' order matches the outline (as usual).

2. Each dimension is covered (as usual), by at least default/top (ordummy for attribute dimension) member.

3. The members for each dimension are sorted by their memnoX numbers inascending order and are not duplicated.

4. For ASO, any member can present.

5. For BSO, the system shall process sparse and dense dimensions'members that comply one of the following:

a. Stored members.

b. Dynamic members with the following limitations on their sub-tree: noformulas, sum-only (“+” or “−”) aggregation, BSO solve-order must be 0.

c. Members defined in transparent partition target that Dynamic Flowdoes not expand, i.e. these members shall be taken from partitionregardless to other dimensions (e.g. partition on one dimension).

CoordType

In accordance with an example embodiment, this is an offset type of amember inside its dimension component in an odometer. The array of theseoffsets determines the position of CMI inside the odometer.

CMI

In accordance with an example embodiment, CMI is a current member[index] combination of a cell, or [rarely] a sub-cube (if somedimensions are ignored). CMI can be determined either by CoordTypearray, or by the actual pointers to the members pointed by the odometer.

Key Space

In accordance with an example embodiment, a cell key is an integerscalar value [of specific width] corresponding to CMI's position insidethe odometer. To provide both directions of this correspondence, thiscorrespondence is a bijection. Typically, the forward transformation isa linear combination of vector of offsets inside the odometer withper-odometer vector of weights (weight per dimension component). Thereverse is division with remainder by the vector of weights in specificorder. The key width, the weights (and dimension order) are parts ofspecific Key Space instance. The Key Space instance translates CMI to akey and vice versa.

Main Module Flow (BSO)

In accordance with an example embodiment, a main module flow (BSO) caninclude:

1. Intersect all transparent partition target regions with the odometer.

2. Obtain all remote connections.

a. For each connection, there can be a number of remote odometers.

3. For each remote connection:

a. Create Cells structures for cells, and create remapping structures.They can be either per remote connection or per remote odometer.

b. Start remote transaction.

c. For each remote odometer of that remote connection:

i. Remap remote odometer's members according to transparent partitiondefinition.

ii. Fetch remote odometer cells to Cells structures.

d. Close remote transaction.

4. Sequentially remap all received Cells structures (eliminating morethan one remappings), like in Blocks Scan below, probably grouping cells“per block” for more effective remapping, and form resulting cells.

a. If the odometer was expanded, remap to the original odometercoordinates.

b. This handles dynamic calculations partially (as blocks may happen notto have local data).

Main Module Flow (ASO)

In accordance with an example embodiment, for choosing the existingentry point function/class, at the first getNext( ) call, the systemissues a wrapper function to fetch all needed cells into aso.buffer. ThegetNext( ) call gets next cell from aso.buffer. The key is decoded tocoordinates. These coordinates are checked in isBelongToOutputBucket( )against param.outputCoords for OUTPUT_COORD mask. If this cell shouldnot go to param.outputBucket, it's immediately returned by the currentgetNext( ) call. Otherwise, the cell is put by putOutputBucket( ) to theparam.outputBucket, and the system loops over another cell fromaso.buffer until there are no more cells.

The present invention may be conveniently implemented using one or moreconventional general purpose or specialized computer, computing device,machine, or microprocessor, including one or more processors, memoryand/or computer readable storage media programmed according to theteachings of the present disclosure. Appropriate software coding canreadily be prepared by skilled programmers based on the teachings of thepresent disclosure, as will be apparent to those skilled in the softwareart.

In some embodiments, the present invention includes a computer programproduct which is a non-transitory storage medium or computer readablemedium (media) having instructions stored thereon/in which can be usedto program a computer to perform any of the processes of the presentinvention. The storage medium can include, but is not limited to, anytype of disk including floppy disks, optical discs, DVD, CD-ROMs,microdrive, and magneto-optical disks, ROMs, RAMs, EPROMs, EEPROMs,DRAMs, VRAMs, flash memory devices, magnetic or optical cards,nanosystems (including molecular memory ICs), or any type of media ordevice suitable for storing instructions and/or data.

The foregoing description of the present invention has been provided forthe purposes of illustration and description. It is not intended to beexhaustive or to limit the invention to the precise forms disclosed.Many modifications and variations will be apparent to the practitionerskilled in the art.

For example, while many of the embodiments described herein illustratethe use of an Oracle Essbase multidimensional database environment, inaccordance with various embodiments the components, features, andmethods described herein can be used with other types of onlineanalytical processing or multidimensional database computingenvironments.

The embodiments were chosen and described in order to best explain theprinciples of the invention and its practical application, therebyenabling others skilled in the art to understand the invention forvarious embodiments and with various modifications that are suited tothe particular use contemplated. It is intended that the scope of theinvention be defined by the following claims and their equivalents.

What is claimed is:
 1. A system for providing bottom-up aggregation in amultidimensional database environment, comprising: a data storagecontainer including data blocks that represent a multidimensionaldatabase, for at least one of storage or analysis of data; and acomputing device having a processor executing instructions that causethe processor to: receive an input query; define, based on the receivedinput query, a region of data within the multidimensional database whichincludes links to other regions within the multidimensional database;create a data structure that includes pointers to the data blocks;determine, by reference to the data structure, which of the data blockscontribute to processing the input query; and scan the data blocks sodetermined, to calculate an upper-level of data blocks during processingof the input query.
 2. The system of claim 1, wherein the storagecontainer is one of a block storage option, or an aggregate storageoption.
 3. The system of claim 2, wherein a dynamic flow processincludes a marking of upper-level members as dynamic, for use during theprocessing of the input query, and expanding dynamic members down to aselection of aggregated members.
 4. The system of claim 3, wherein thedata structure that includes pointers to the data blocks operates as anodometer that includes an array of arrays of pointers.
 5. The system ofclaim 1, wherein the instructions cause the processor to provide aresponse to the input query, the response including the data blocks inthe upper-level.
 6. A method for providing bottom-up aggregation in amultidimensional database environment, comprising: providing, at acomputer system including a processor, a data storage containerincluding data blocks that represent a multidimensional database, for atleast one of storage or analysis of data; receiving an input query;defining, based on the received input query, a region of data within themultidimensional database which includes links to other regions withinthe multidimensional database; creating a data structure that includespointers to the data blocks; determining, by reference to the datastructure, which of the data blocks contribute to processing the inputquery; and scanning the data blocks so determined, to calculate anupper-level of data blocks during processing of the input query.
 7. Themethod of claim 6, wherein the storage container is one of a blockstorage option, or an aggregate storage option.
 8. The method of claim7, wherein a dynamic flow process includes a marking of upper-levelmembers as dynamic, for use during the processing of the input query,and expanding dynamic members down to a selection of aggregated members.9. The method of claim 8, wherein the data structure that includespointers to the data blocks operates as an odometer that includes anarray of arrays of pointers.
 10. The method of claim 6, wherein theinstructions cause the processor to provide a response to the inputquery, the response including the data blocks in the upper-level.
 11. Anon-transitory computer readable storage medium, including instructionsstored thereon which when read and executed by one or more computerscause the one or more computers to perform the method comprising:providing, at a computer system including a processor, a data storagecontainer including data blocks that represent a multidimensionaldatabase, for at least one of storage or analysis of data; receiving aninput query; defining, based on the received input query, a region ofdata within the multidimensional database which includes links to otherregions within the multidimensional database; creating a data structurethat includes pointers to the data blocks; determining, by reference tothe data structure, which of the data blocks contribute to processingthe input query; and scanning the data blocks so determined, tocalculate an upper-level of data blocks during processing of the inputquery.
 12. The non-transitory computer readable storage medium of claim11, wherein the storage container is one of a block storage option, oran aggregate storage option.
 13. The non-transitory computer readablestorage medium of claim 12, wherein a dynamic flow process includes amarking of upper-level members as dynamic, for use during the processingof the input query, and expanding dynamic members down to a selection ofaggregated members.
 14. The non-transitory computer readable storagemedium of claim 13, wherein the data structure that includes pointers tothe data blocks operates as an odometer that includes an array of arraysof pointers.
 15. The non-transitory computer readable storage medium ofclaim 11, wherein the instructions cause the processor to provide aresponse to the input query, the response including the data blocks inthe upper-level.